package com.huan.study.excel.example;

import com.huan.study.excel.entity.StudentInfo;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 读取合并的单元格
 *
 * @author huan.fu
 * @date 2024/3/23 - 22:36
 */
public class ReadMergeCellInfo {

    public static void main(String[] args) throws IOException {

        try (InputStream is = ReadMergeCellInfo.class.getClassLoader().getResourceAsStream("excel/学生信息.xlsx"); Workbook workbook = WorkbookFactory.create(is)) {
            Sheet sheet = workbook.getSheetAt(0);
            List<StudentInfo> studentInfos = new ArrayList<>(10);

            for (Row row : sheet) {
                Long id = null;
                String className = null;
                String studentName = null;
                Integer age = null;

                // 跳过第一行，第一行为标题行
                if (row.getRowNum() == 0) {
                    continue;
                }

                for (Cell cell : row) {
                    int columnIndex = cell.getColumnIndex();
                    switch (columnIndex) {
                        case 0:
                            id = (long) cell.getNumericCellValue();
                            break;
                        case 1:
                            boolean mergedCell = false;
                            // 检查当前单元格是否是合并单元格的一部分
                            for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                                if (mergedRegion.isInRange(row.getRowNum(), columnIndex)) {
                                    // 获取合并区域的左上角单元格的值
                                    Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
                                    Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                                    className = firstCell.getStringCellValue();
                                    mergedCell = true;
                                    break;
                                }
                            }
                            if (!mergedCell) {
                                className = cell.getStringCellValue();
                            }
                            break;
                        case 2:
                            studentName = cell.getRichStringCellValue().getString();
                            break;
                        case 3:
                            boolean ageMergedCell = false;
                            // 检查当前单元格是否是合并单元格的一部分
                            for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
                                if (mergedRegion.isInRange(row.getRowNum(), columnIndex)) {
                                    // 获取合并区域的左上角单元格的值
                                    Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
                                    Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                                    age = (int) firstCell.getNumericCellValue();
                                    ageMergedCell = true;
                                    break;
                                }
                            }
                            if (!ageMergedCell) {
                                age = (int) cell.getNumericCellValue();
                            }
                            break;
                        default:
                            break;
                    }
                }
                studentInfos.add(new StudentInfo(id, className, studentName, age));
            }

            for (StudentInfo studentInfo : studentInfos) {
                System.out.println(studentInfo);
            }
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        }
    }
}
